import pymysql,re
dbhost='localhost'
dbuser='root'
dbpassword='123456'
dbdatabase='renren_fast'
def toUnderline(x):
	"""转下划线命名"""
	return re.sub('(?<=[a-z])[A-Z]|(?<!^)[A-Z](?=[a-z])', '_\g<0>', x).lower()
def toCamelcase(x):
	"""转小驼峰法命名"""
	s = re.sub('_([a-zA-Z])', lambda m: (m.group(1).upper()), x.lower())
	return s[0].lower() + s[1:]	
def toUpperCamelcase(x):
	"""转大驼峰法命名"""
	s = re.sub('_([a-zA-Z])', lambda m: (m.group(1).upper()), x.lower())
	return s[0].upper() + s[1:]

def ListAllTable(prefix=''):
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	cursor.execute("Show tables;")
	dataList = cursor.fetchall()
	tables=[]
	for row in dataList:
		if row[0].startswith(prefix):tables.append(row[0])
	return tables
def ListTableFields(TableName='sys_user'):
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	sql = "select * from "+TableName
	result = cursor.execute(sql)
	desc = cursor.description
	fields=[]
	for field in desc:
		# print(field)
		fields.append(field[0])
		
	# sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = 'sys_user'"
	# cursor.execute(sql)
	# for field in cursor.fetchall():
		# print(field)
	cursor.close()
	db.close()
	return fields
def ListTableFieldsWithComments(TableName='sys_role'):
	db = pymysql.connect(host=dbhost,user=dbuser,password=dbpassword,database=dbdatabase)
	cursor = db.cursor()
	cursor.execute('select column_name,column_comment from information_schema.COLUMNS where table_schema="'+dbdatabase+'" and TABLE_NAME=%s',TableName)
	columns = cursor.fetchall()
	returncolumns=[]
	# print(columns)
	for column in columns:
		comment = column[1]
		comment = comment.replace('-', '')
		comment = comment.replace(':', '，')
		comment = comment.replace(',', '，')
		comment = comment.replace(' ', '，')
		comment = comment.replace('=', '，')
		comment = comment.replace('(', '（')
		comment = comment.replace(')', '）')
		comment = comment.replace('[', '（')
		comment = comment.replace(']', '）')
		comment = comment.replace('|', '、')
		comment = comment.replace('"', '')
		comment = comment.replace('.', '、')
		comment = comment.replace('/', '、')
		comment = comment.replace('{', '（')
		comment = comment.replace('}', '）')
		comment = comment.replace('?', '？')
		returncolumns.append([column[0],comment])
		# print(comment)
	return returncolumns
def GetListPara(columns):
	likeColumn=columns[1] #search column
	likeColumnArg=toCamelcase(columns[1]) #search column
	listRecorder="{"
	for i in range(len(columns)):
		if i<len(columns)-1:
			listRecorder+="'"+toCamelcase(columns[i])+"':dataList1[i]["+str(i)+"],"
		else:
			listRecorder+="'"+toCamelcase(columns[i])+"':dataList1[i]["+str(i)+"]"
	listRecorder+="}"
	return likeColumn,likeColumnArg,listRecorder
def GetSavePara(columns):
	saveArgs='''if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))\n'''
	for i in range(len(columns)):
		if i>0:
			saveArgs+="		if '"+toCamelcase(columns[i])+"' in d.keys():"+toCamelcase(columns[i])+" = d['"+toCamelcase(columns[i])+"']\n"
			saveArgs+="		else:"+toCamelcase(columns[i])+"=''\n"
	saveArgs+="	elif request.form:\n"
	for i in range(len(columns)):
		if i>0:
			saveArgs+="		"+toCamelcase(columns[i])+" = request.form['"+toCamelcase(columns[i])+"']\n"
	saveSql="sql = 'insert into ${tableName} ("+",".join(columns[1:])+") values('"
	for i in range(1,len(columns)):
		if i<len(columns)-1:
			saveSql+='''+"'"+str('''+toCamelcase(columns[i])+''')+"',"'''
		else:
			saveSql+='''+"'"+str('''+toCamelcase(columns[i])+''')+"')"'''
	return saveArgs,saveSql
def GetUpdatePara(columns):
	updateArgs='''if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))\n'''
	for i in range(len(columns)):
		updateArgs+="		if '"+toCamelcase(columns[i])+"' in d.keys():"+toCamelcase(columns[i])+" = d['"+toCamelcase(columns[i])+"']\n"
		updateArgs+="		else:"+toCamelcase(columns[i])+"=''\n"
	updateArgs+="	elif request.form:\n"
	for i in range(len(columns)):
		updateArgs+="		"+toCamelcase(columns[i])+" = request.form['"+toCamelcase(columns[i])+"']\n"

	updateSql='''sql ="UPDATE ${tableName} SET '''
	for i in range(1,len(columns)):
		if i<len(columns)-1:
			updateSql+=columns[i]+'''='"+str('''+toCamelcase(columns[i])+''')+"','''
		else:
			updateSql+=columns[i]+'''='"+str('''+toCamelcase(columns[i])+''')+"'"'''
	updateSql+='''+ "WHERE '''+columns[0]+''' = '"+str('''+toCamelcase(columns[0])+''')+"'"'''
	return updateArgs,updateSql
def GetDeletePara(columns):
	deleteArgs='''if request.data:
		if printrequestdata:print(request.data)
		d=json.loads(str(request.data, 'UTF-8'))\n'''
	deleteArgs+="		if '"+toCamelcase(columns[0])+"' in d.keys():"+toCamelcase(columns[0])+" = d['"+toCamelcase(columns[0])+"']\n"
	deleteArgs+="		else:"+toCamelcase(columns[0])+"=''\n"
	deleteArgs+="	elif request.form:\n"
	deleteArgs+="		"+toCamelcase(columns[0])+" = request.form['"+toCamelcase(columns[0])+"']\n"
	
	deleteSql='''sql ="DELETE FROM ${tableName} WHERE '''+columns[0]+'''='"+str('''+toCamelcase(columns[0])+''')+"'"'''
	return deleteArgs,deleteSql
def GetInfoPara(columns):
	infoSql='''sql ="SELECT * from ${tableName} where  '''+columns[0]+'''='"+str(id)+"'"'''
	infoRecorder="{"
	for i in range(len(columns)):
		if i<len(columns)-1:
			infoRecorder+="'"+toCamelcase(columns[i])+"':dataList1[0]["+str(i)+"],"
		else:
			infoRecorder+="'"+toCamelcase(columns[i])+"':dataList1[0]["+str(i)+"]"
	infoRecorder+="}"
	return infoSql,infoRecorder	
def GetSelectPara(columns):
	selectRecorder="{"
	for i in range(len(columns)):
		if i<len(columns)-1:
			selectRecorder+="'"+toCamelcase(columns[i])+"':dataList1[i]["+str(i)+"],"
		else:
			selectRecorder+="'"+toCamelcase(columns[i])+"':dataList1[i]["+str(i)+"]"
	selectRecorder+="}"
	return selectRecorder	
def GeneratePyCode(TableName='sys_user'):
	moduleName=TableName.split('_')[0]
	className=TableName.split('_')[1]
	
	columns=ListTableFields(TableName)
	# print(columns)
	likeColumn,likeColumnArg,listRecorder=GetListPara(columns)
	saveArgs,saveSql=GetSavePara(columns)
	updateArgs,updateSql=GetUpdatePara(columns)
	deleteArgs,deleteSql=GetDeletePara(columns)
	infoSql,infoRecorder=GetInfoPara(columns)
	selectRecorder=GetSelectPara(columns)
	
	f = open('./template/className.py', mode = "r")
	content = f.read()
	f.close()
	
	content=content.replace("${likeColumn}",likeColumn)
	content=content.replace("${likeColumnArg}",likeColumnArg)
	content=content.replace("${listRecorder}",listRecorder)
	content=content.replace("${saveArgs}",saveArgs)
	content=content.replace("${saveSql}",saveSql)
	content=content.replace("${updateArgs}",updateArgs)
	content=content.replace("${updateSql}",updateSql)
	content=content.replace("${deleteArgs}",deleteArgs)
	content=content.replace("${deleteSql}",deleteSql)
	content=content.replace("${infoSql}",infoSql)
	content=content.replace("${infoRecorder}",infoRecorder)
	content=content.replace("${selectRecorder}",selectRecorder)
	
	content=content.replace("${moduleName}",moduleName,)
	content=content.replace("${className}",className)
	content=content.replace("${tableName}",TableName)
	# print(content)
	
	f = open('./GeneratedCode/'+className+'.py', mode = "w")
	f.write(content)
	f.close()

def GetListVuePara(columns):
	TableColumns=""
	for i in range(len(columns)):
		TableColumns+='''		<el-table-column\n'''
		TableColumns+='''		prop="'''+toCamelcase(columns[i][0])+'''"\n'''
		TableColumns+='''		header-align="center"\n'''
		TableColumns+='''		align="center"\n'''
		if columns[i][1]:
			TableColumns+='''		label="'''+columns[i][1]+'''">\n'''
		else:
			TableColumns+='''		label="'''+toCamelcase(columns[i][0])+'''">\n'''
		TableColumns+='''		</el-table-column>\n'''
	# print(TableColumns)
	listLikeColumn=toCamelcase(columns[1][0])
	idColumn=toCamelcase(columns[0][0])
	# print(listLikeColumn,idColumn)
	return listLikeColumn,idColumn,TableColumns
def GenerateListVueCode(TableName='sys_user'):
	moduleName=TableName.split('_')[0]
	className=TableName.split('_')[1]
	
	columns=ListTableFieldsWithComments(TableName)
	listLikeColumn,idColumn,TableColumns=GetListVuePara(columns)
	
	f = open('./template/className.vue', mode = "r", encoding='utf-8')
	content = f.read()
	f.close()
	
	content=content.replace("${listLikeColumn}",listLikeColumn)
	content=content.replace("${idColumn}",idColumn)
	content=content.replace("${TableColumns}",TableColumns)
	
	content=content.replace("${moduleName}",moduleName,)
	content=content.replace("${className}",className)
	content=content.replace("${tableName}",TableName)
	
	# print(content)
	f = open('./GeneratedCode/'+className+'.vue', mode = "w",encoding='utf-8')
	f.write(content)
	f.close()

def GetSaveUpdateVuePara(columns):
	TableColumns=""
	for i in range(1,len(columns)):
		TableColumns+='''		<el-form-item label="'''+columns[i][1]+'''" prop="'''+toCamelcase(columns[i][0])+'''">\n'''
		TableColumns+='''			<el-input v-model="dataForm.'''+toCamelcase(columns[i][0])+'''" placeholder="'''+columns[i][1]+'''"></el-input>\n'''
		TableColumns+='''		</el-form-item>\n'''
	
	dataFormColumns=""
	for i in range(len(columns)):
		if i==0:
			dataFormColumns+="id: 0,\n"
		elif i<len(columns)-1:
			dataFormColumns+=toCamelcase(columns[i][0])+": '',\n"
		else:
			dataFormColumns+=toCamelcase(columns[i][0])+": ''\n"
	
	dataRuleColumns=""
	for i in range(1,len(columns)):
		if i<len(columns)-1:
			dataRuleColumns+=toCamelcase(columns[i][0])+": [{ required: true, message: '"+columns[i][1]+"不能为空', trigger: 'blur' }],\n"
		else:
			dataRuleColumns+=toCamelcase(columns[i][0])+": [{ required: true, message: '"+columns[i][1]+"不能为空', trigger: 'blur' }]\n"
	
	
	dataFormAsignColumns=""
	for i in range(1,len(columns)):
		dataFormAsignColumns+="this.dataForm."+toCamelcase(columns[i][0])+" = data.${className}."+toCamelcase(columns[i][0])+"\n"
		
	submitColumns=""
	for i in range(len(columns)):
		if i==0:
			submitColumns+="'"+toCamelcase(columns[i][0])+"': this.dataForm.id || undefined,\n"
		elif i<len(columns)-1:
			submitColumns+="'"+toCamelcase(columns[i][0])+"': this.dataForm."+toCamelcase(columns[i][0])+",\n"
		else:
			submitColumns+="'"+toCamelcase(columns[i][0])+"': this.dataForm."+toCamelcase(columns[i][0])+"\n"
	
	idColumn="id"
	return TableColumns,dataFormColumns,dataRuleColumns,dataFormAsignColumns,submitColumns,idColumn
def GenerateSaveUpdateVueCode(TableName='sys_user'):
	moduleName=TableName.split('_')[0]
	className=TableName.split('_')[1]
	
	columns=ListTableFieldsWithComments(TableName)
	TableColumns,dataFormColumns,dataRuleColumns,dataFormAsignColumns,submitColumns,idColumn=GetSaveUpdateVuePara(columns)
	
	f = open('./template/className-add-or-update.vue', mode = "r", encoding='utf-8')
	content = f.read()
	f.close()
	
	content=content.replace("${TableColumns}",TableColumns)
	content=content.replace("${dataFormColumns}",dataFormColumns)
	content=content.replace("${dataRuleColumns}",dataRuleColumns)
	content=content.replace("${dataFormAsignColumns}",dataFormAsignColumns)
	content=content.replace("${submitColumns}",submitColumns)
	content=content.replace("${idColumn}",idColumn)
	
	content=content.replace("${moduleName}",moduleName,)
	content=content.replace("${className}",className)
	content=content.replace("${tableName}",TableName)
	
	# print(content)
	f = open('./GeneratedCode/'+className+'-add-or-update.vue', mode = "w",encoding='utf-8')
	f.write(content)
	f.close()

def GenerateCode(TableName='sys_user'):
	GeneratePyCode(TableName)
	GenerateSaveUpdateVueCode(TableName)
	GenerateListVueCode(TableName)
def test():
	# print(toUnderline("userName"))
	# print(toCamelcase("user_name"))
	# print(ListAllTable(prefix='sys'))
	# print(ListTableFields(tablename='sys_user'))
	# print(moduleName,className,likeColumn)
	# print(GetListPara()[0])
	# print(GetListPara()[1])
	# print(GetSavePara()[0])
	# print(GetSavePara()[1])
	# print(GetUpdatePara()[0])
	# print(GetUpdatePara()[1])
	# print(GetDeletePara()[0])
	# print(GetDeletePara()[1])
	# print(GetInfoPara()[0])
	# print(GetInfoPara()[1])
	# print(GetSelectPara())
	# GetListVuePara(ListTableFieldsWithComments(TableName='sys_role'))
	# GetSaveUpdateVuePara(ListTableFieldsWithComments(TableName='sys_role'))
	# print(ListTableFieldsWithComments())
	pass
if __name__ == '__main__':
	#说明：根据数据库表信息生成单表的增删改查代码。
	# usage:
		# 1.set db info on file top
		# 2.generate according to table name
	print(ListAllTable(prefix='sys'))
	# for tablename in ['blog_article', 'blog_category', 'blog_comment', 'blog_tags']:
	for tablename in ['sys_config', 'sys_log']:
		GenerateCode(TableName=tablename)